﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

namespace Hotel
{
    class CNDatabase
    {
        public static string UserName = string.Empty;
        public SqlConnection cn = new SqlConnection();

        public void connect()
        {

            cn.ConnectionString = @"Data Source=ASUSSERIAL;Initial Catalog=DataHotel;Integrated Security=True";
            cn.Open();
        }

        public void closeConnect()
        {
            cn.Close();
        }
        
        // get ONE 
        public object getOneData(string str)
        {
            CNDatabase cnd = new CNDatabase();
            cnd.connect();
            SqlCommand cm = new SqlCommand(str, cnd.cn);
            object obj = cm.ExecuteScalar();
            cnd.cn.Close();
            return obj;
        }
        
        public object xlExecuteScalar(string query)
        {
            connect();
            SqlCommand comm = new SqlCommand();
            comm.CommandText = query;
            comm.Connection = cn;
            object abc = comm.ExecuteScalar();
            cn.Close();
            return abc;
        }
       
        //Insert, uodate, delele
        //ex: xlExecuteNonQuery("insert into.....")
        public void xlExecuteNonQuery(string query)
        {
            connect();
            SqlCommand comm = new SqlCommand();
            comm.CommandText = query;
            comm.Connection = cn;
            comm.ExecuteNonQuery();
            cn.Close();
         }

        //  get List data
        public BindingSource getData(string query)
        {
            connect();
            DataTable tb = new DataTable();
            SqlDataAdapter dta = new SqlDataAdapter(query, cn);
            dta.Fill(tb);
            BindingSource bdS = new BindingSource();
            bdS.DataSource = tb;
            cn.Close();
            return bdS;
            
        }
        public DataSet getDataset(string query) { 
            connect();
            DataSet ds = new DataSet();
            SqlDataAdapter dta = new SqlDataAdapter(query, cn);
            dta.Fill(ds);
            closeConnect();
            return ds;
        }

    }

}
